﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using Shl01.Models;
using System.Data;

namespace Shl01.Dal
{
    public class BiaoShuoMIng
    {
        public static void Update(DataTable dt, Conn conn, string DatabaseName)
        {
            string TableName = dt.Rows[0]["表名"].ToString();
            try
            {
                DeleteTable(TableName, conn, DatabaseName);
            }
            catch (Exception ex)
            {

            }
            if(dt.Rows[0]["表说明"].ToString().Trim().Length > 0)
                AddTable(TableName, dt.Rows[0]["表说明"].ToString(), conn, DatabaseName);
            foreach (DataRow dr in dt.Rows)
            {
                try
                {
                    DeleteColumn(TableName, dr["字段名"].ToString(), conn, DatabaseName);
                }
                catch (Exception ex)
                {

                }
                if (dr["字段说明"].ToString().Trim().Length > 0)
                    AddColumn(TableName, dr["字段名"].ToString(), dr["字段说明"].ToString(), conn, DatabaseName);
            }

        }
        public static void DeleteTable(string TableName, Conn con, string DatabaseName)
        {
            string sql = string.Format("EXECUTE sp_dropextendedproperty N'{0}1', N'user', N'dbo', N'table', N'{0}', NULL, NULL", TableName);
            string ConnStr = "";
            if (con.Type == 0)
            {
                ConnStr = string.Format("serVer = {0}; database = {3};uid = {1};pwd = {2}", con.Fuwu, con.Id, con.Pwd, DatabaseName);
            }
            else
            {
                ConnStr = string.Format("Data Source={0};Initial Catalog={1};Integrated Security=True", con.Fuwu, DatabaseName);
            }
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.ExecuteNonQuery();
                }
            }
        }
        public static void DeleteColumn(string TableName, string column, Conn con, string DatabaseName)
        {
            string sql = string.Format("execute sp_dropextendedproperty N'{1}1', N'user', N'dbo', N'table', N'{0}', N'column', N'{1}'", TableName, column);
            string ConnStr = "";
            if (con.Type == 0)
            {
                ConnStr = string.Format("serVer = {0}; database = {3};uid = {1};pwd = {2}", con.Fuwu, con.Id, con.Pwd, DatabaseName);
            }
            else
            {
                ConnStr = string.Format("Data Source={0};Initial Catalog={1};Integrated Security=True", con.Fuwu, DatabaseName);
            }
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.ExecuteNonQuery();
                }
            }
        }
        public static void AddColumn(string TableName, string column, string ShuoMing, Conn con, string DatabaseName)
        {
            string sql = string.Format("EXECUTE sp_addextendedproperty N'{2}1', '{0}', N'user', N'dbo', N'table', N'{1}', N'column', N'{2}'", ShuoMing, TableName, column);
            string ConnStr = "";
            if (con.Type == 0)
            {
                ConnStr = string.Format("serVer = {0}; database = {3};uid = {1};pwd = {2}", con.Fuwu, con.Id, con.Pwd, DatabaseName);
            }
            else
            {
                ConnStr = string.Format("Data Source={0};Initial Catalog={1};Integrated Security=True", con.Fuwu, DatabaseName);
            }
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.ExecuteNonQuery();
                }
            }
        }
        public static void AddTable(string TableName, string ShuoMing, Conn con, string DatabaseName)
        {
            string sql = string.Format("EXECUTE sp_addextendedproperty N'{1}1', N'{0}', N'user', N'dbo', N'table', N'{1}', NULL, NULL", ShuoMing, TableName);
            string ConnStr = "";
            if (con.Type == 0)
            {
                ConnStr = string.Format("serVer = {0}; database = {3};uid = {1};pwd = {2}", con.Fuwu, con.Id, con.Pwd, DatabaseName);
            }
            else
            {
                ConnStr = string.Format("Data Source={0};Initial Catalog={1};Integrated Security=True", con.Fuwu, DatabaseName);
            }
            using (SqlConnection conn = new SqlConnection(ConnStr))
            {
                conn.Open();
                using (SqlCommand cmd = new SqlCommand(sql, conn))
                {
                    cmd.ExecuteNonQuery();
                }
            }
        }
    }
}
